package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;

import model.ItemTypeMeasurement;

public class DBItemTypeMeasurement implements IFItemTypeMeasurement
{
	private Connection con;

	public DBItemTypeMeasurement()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createItemTypeMeasurement(ItemTypeMeasurement newItemTypeMeasurement) throws SQLException
	{
		int rc = -1;

		String query = "SELECT im_id FROM item_measurement WHERE im_measurement = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createItemTypeMeasurement", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, newItemTypeMeasurement.getMeasurement());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO item_measurement(im_measurement) VALUES(?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createItemTypeMeasurement", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, newItemTypeMeasurement.getMeasurement());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createItemTypeMeasurement - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createItemTypeMeasurement - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateItemTypeMeasurement(ItemTypeMeasurement newItemTypeMeasurement) throws SQLException
	{
		int rc = -1;

		String query = "SELECT im_id FROM item_measurement WHERE im_measurement = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateItemTypeMeasurement", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, newItemTypeMeasurement.getMeasurement());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE item_measurement SET im_measurement = ? WHERE im_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateItemTypeMeasurement", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, newItemTypeMeasurement.getMeasurement());
					ps.setInt(2, newItemTypeMeasurement.getID());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateItemTypeMeasurement - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateItemTypeMeasurement - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public ItemTypeMeasurement findItemTypeMeasurementByName(String typeMeasurement) throws SQLException
	{
		ItemTypeMeasurement newItemTypeMeasurement = new ItemTypeMeasurement();

		String query = "SELECT * FROM item_measurement WHERE im_measurement = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("findItemTypeMeasurementByName", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, typeMeasurement);

			ResultSet results = ps.executeQuery();

			if (results.next())
				newItemTypeMeasurement = buildItemTypeMeasurementObject(results);

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - findItemTypeMeasurementByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newItemTypeMeasurement;
	}

	@Override
	public ArrayList<ItemTypeMeasurement> printAllItemTypeMeasurements() throws SQLException
	{
		ArrayList<ItemTypeMeasurement> itemTypesMeasurementList = new ArrayList<ItemTypeMeasurement>();

		String query = "SELECT * FROM item_measurement ORDER BY im_measurement ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllItemTypeMeasurements", query);

		try 
		{	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);

			ResultSet results = stmt.executeQuery(query);

			while(results.next())
			{				
				ItemTypeMeasurement newItemTypeMeasurement = buildItemTypeMeasurementObject(results);

				itemTypesMeasurementList.add(newItemTypeMeasurement);
			}

			stmt.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllItemTypeMeasurements - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return itemTypesMeasurementList;
	}

	private ItemTypeMeasurement buildItemTypeMeasurementObject(ResultSet results) throws SQLException
	{
		ItemTypeMeasurement newItemTypeMeasurement = new ItemTypeMeasurement();

		newItemTypeMeasurement.setID(results.getInt("im_id"));
		newItemTypeMeasurement.setMeasurement(results.getString("im_measurement"));

		return newItemTypeMeasurement;
	}
}